﻿Imports System.Data.OleDb
Imports DTO

Public Class HoaDonDAO

    Public Function ThemHoaDon(ByVal hd As HoaDonDTO, ByVal cthd As ChiTietHoaDonDTO) As Boolean
        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "insert into HoaDon(idKhachHang,NgayLapHD,UserName,DaXoa) values(?,?,?,?)"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idKhachHang", OleDbType.Integer)
        cmd.Parameters.Add("@NgayLapHoaDon", OleDbType.Date)
        cmd.Parameters.Add("@UserName", OleDbType.WChar)
        cmd.Parameters.Add("@DaXoa", OleDbType.WChar)
        cmd.Parameters("@idKhachHang").Value = hd.layIDKhacHang
        cmd.Parameters("@NgayLapHoaDon").Value = hd.NgayLapHoaDon
        cmd.Parameters("@UserName").Value = hd.LayUserName
        cmd.Parameters("@DaXoa").Value = 0
        cmd.ExecuteNonQuery()

        sql = "Select @@IDENTITY"
        cmd = New OleDbCommand(sql, cn)
        hd.LayIDHoaDon = cmd.ExecuteScalar()

        cthd.LayIDHoaDon = hd.LayIDHoaDon

        Dim cthd1 As New ChiTietHoaDonDAO()
        cthd1.ThemChiTietHoaDon(cthd)
        cn.Close()

        Return kq
    End Function

    Public Function LayDanhSachHoaDon() As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "select idHoaDon,HoaDon.idKhachHang, TenKhachHang,NgayLapHD,UserName from  KhachHang ,HoaDon where hoadon.DaXoa=0 and khachhang.idkhachhang = hoadon.idkhachhang  "
        Dim a As New OleDb.OleDbDataAdapter(sql, cn)
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    Public Function XoaHoaDon(ByVal hd As HoaDonDTO) As Boolean
        Dim kq As Boolean = True
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "update HoaDon set DaXoa=1 where idHoaDon= ?"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idHoaDon", OleDbType.Integer)

        cmd.Parameters("@idHoaDon").Value = hd.LayIDHoaDon
        cmd.ExecuteNonQuery()
        cn.Close()
        Return kq

    End Function

    Public Function DemHoaDon(ByVal hd As HoaDonDTO) As Boolean
        Dim kq As Boolean
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "Select *  From HoaDon where idKhachHang= ? and NgayLapHD= ?"
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idKhachHang", OleDbType.Integer)
        cmd.Parameters("@idKhachHang").Value = hd.layIDKhacHang
        cmd.Parameters.Add("@NgayLapHoaDon", OleDbType.Date)
        cmd.Parameters("@NgayLapHoaDon").Value = hd.LayNgayLapHoaDon
        cmd.ExecuteNonQuery()
        cn.Close()
        Return kq
    End Function

    Public Function LayIDHoaDon(ByVal hd As HoaDonDTO) As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "Select *  From HoaDon where idKhachHang= ? "
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@idKhachHang", OleDbType.Integer)
        cmd.Parameters("@idKhachHang").Value = hd.layIDKhacHang
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    Public Function IDHoaDon(ByVal hd As HoaDonDTO) As DataTable
        Dim kq As New DataTable
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.ConnectionData()
        'B3: Tao chuoi strSQL thao tac CSDL
        Dim sql As String = "Select *  From HoaDon where idHoaDon= ? "
        Dim cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.Add("@id", OleDbType.Integer)
        cmd.Parameters("@id").Value = hd.LayIDHoaDon
        Dim a As New OleDb.OleDbDataAdapter()
        a.SelectCommand = cmd
        a.Fill(kq)
        cn.Close()
        Return kq
    End Function

    'Public Shared Function ThemHoaDon2(ByVal hd As HoaDonDTO) As Boolean
    '    Dim kq As Boolean = True
    '    Dim cn As OleDbConnection
    '    'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
    '    cn = DatabaseProvider.ConnectionData()
    '    'B3: Tao chuoi strSQL thao tac CSDL
    '    Dim sql As String = "insert into HoaDon(idKhachHang,NgayLapHD,UserName,DaXoa) values(?,?,?,?)"
    '    Dim cmd As New OleDbCommand(sql, cn)
    '    cmd.Parameters.Add("@idKhachHang", OleDbType.Integer)
    '    cmd.Parameters.Add("@NgayLapHoaDon", OleDbType.Date)
    '    cmd.Parameters.Add("@UserName", OleDbType.WChar)
    '    cmd.Parameters.Add("@DaXoa", OleDbType.WChar)
    '    cmd.Parameters("@idKhachHang").Value = hd.layIDKhacHang
    '    cmd.Parameters("@NgayLapHoaDon").Value = hd.NgayLapHoaDon
    '    cmd.Parameters("@UserName").Value = hd.LayUserName
    '    cmd.Parameters("@DaXoa").Value = 0
    '    cmd.ExecuteNonQuery()
    '    cn.Close()
    '    Return kq

    'End Function

End Class
